

* IMPORT AMNESTY 2002 DATA

import excel "$data\irregulars_singles_prov-nat-gender_2002.xls", sheet("Sheet1") firstrow clear
gen year =2002
collapse (sum) males females , by(prov nationality year)
preserve
keep prov nationality males year
rename prov provres
rename nationality hbc
rename males Msingle_tilde2002
drop if Msingle_tilde2002==0
save "$data\Msingle_IRREG2002.dta" , replace
restore

preserve
keep prov nationality females year
rename prov provres
rename nationality wbc
rename females Fsingle_tilde2002
drop if Fsingle_tilde2002==0
save "$data\Fsingle_IRREG2002.dta" , replace
restore


* Single males

use "$data\Msinglemag_country_alltime.dta" , clear
rename Msingle Msingle_star
drop if Msingle_star==.
gen legal= hbc==0 | hbc==203 | hbc==206 | hbc==212 | hbc==214 | hbc==215 | hbc==216 | hbc==220 | hbc==221 | hbc==226 | hbc==232 | hbc==234 | hbc==219 | hbc==239 | hbc==240 
tab legal

preserve
keep if legal==1
rename  Msingle_star Msingle
save "$data\Msingle_REGULARS.dta" , replace
restore

drop if legal==1

* PER 2002 GENERATE gamma(gender) and alpha(provres hbc gender)
preserve
keep if year==2002
merge 1:1 provres hbc using "$data\Msingle_IRREG2002.dta"
tab _merge
recode Msingle_tilde2002( .=0) if _merge==1
recode Msingle_star ( .=1) 
gen legal2= hbc==0 | hbc==203 | hbc==206 | hbc==212 | hbc==214 | hbc==215 | hbc==216 | hbc==220 | hbc==221 | hbc==226 | hbc==232 | hbc==234 | hbc==219 | hbc==239 | hbc==240 
drop if legal2==1
drop _merge
egen Sum_Msingle_tilde=total(Msingle_tilde2002)
egen Sum_Msingle_star = total(Msingle_star)
gen gamma = (Sum_Msingle_tilde / (Sum_Msingle_tilde+Sum_Msingle_star))/0.34
di gamma
bys provres hbc : gen alpha = Msingle_tilde2002/ Msingle_star
save "$data\irregulars_2002.dta" , replace
restore

merge m:1 provres hbc using "$data\irregulars_2002.dta", keepusing(alpha gamma Msingle_tilde2002)
tab _merge
recode gamma (.=1.2018698) if _merge==1
recode alpha (.=0) 
drop _merge
recode Msingle_star (.=0)
gen EU2004= hbc==257 | hbc==315 | hbc==247 | hbc==248 | hbc==249 | hbc==227 | hbc==233 | hbc==255 | hbc==251 | hbc==244 
gen EU2007= hbc==209 | hbc==235   
gen migrants=1
replace migrants=0  if EU2004==1&year>=2004
replace migrants=0  if EU2007==1&year>=2007
bys year: egen Sum_Msingle_star = total(Msingle_star) if migrants==1
gen Msingle_star_alpha = Msingle_star*alpha
bys year: egen Sum_Msingle_star_alpha = total(Msingle_star_alpha) if migrants==1

gen theta=.
replace theta=0.40 if year==1995
replace theta=0.25 if year==1996
replace theta=0.12 if year==1997
replace theta=0.19 if year==1998
replace theta=0.16 if year==1999
replace theta=0.11 if year==2000
replace theta=0.23 if year==2001
replace theta=0.23 if year==2003
replace theta=0.09 if year==2004
replace theta=0.15 if year==2005
replace theta=0.19 if year==2006
replace theta=0.10 if year==2007
replace theta=0.17 if year==2008
replace theta=0.10 if year==2009
replace theta=0.10 if year==2010
replace theta=0.09 if year==2011
replace theta=0.07 if year==2012

gen beta = ((gamma*theta)*Sum_Msingle_star)/((1-gamma*theta)*(Sum_Msingle_star_alpha)) if year!=2002
tab beta
replace alpha=0 if  EU2004==1&year>=2004
replace alpha=0 if  EU2007==1&year>=2007
replace beta=1  if  EU2004==1&year>=2004
replace beta=1  if  EU2007==1&year>=2007
save "$data\Msingle_shock.dta" , replace

gen Msingle = (1+alpha*beta)*Msingle_star
replace Msingle = Msingle_star+Msingle_tilde2002 if year==2002
codebook Msingle
append using "$data\Msingle_REGULARS.dta"
order Msingle Msingle_star Msingle_tilde2002 alpha beta gamma theta
save "$data\Msingle_FINAL.dta" , replace


* Robustness: Measurement error in Unofficial Singles 

forval s=10(10)300 {
	
use "$data\Msingle_shock.dta" , clear
gen shock = `s'/100
gen Msingle = (1+alpha*beta*shock)*Msingle_star
replace Msingle = Msingle_star+Msingle_tilde2002 if year==2002
codebook Msingle
append using "$data\Msingle_REGULARS.dta"

save "$data\Msingle_FINAL_shock`s'.dta" , replace
}


* Single females

use "$data\Fsinglemag_country_alltime.dta" , clear
rename Fsingle Fsingle_star
drop if Fsingle_star==.
gen legal= wbc==0 | wbc==203 | wbc==206 | wbc==212 | wbc==214 | wbc==215 | wbc==216 | wbc==220 | wbc==221 | wbc==226 | wbc==232 | wbc==234 | wbc==219 | wbc==239 | wbc==240 
tab legal

preserve
keep if legal==1
rename  Fsingle_star Fsingle
save "$data\Fsingle_REGULARS.dta" , replace
restore

drop if legal==1
* PER 2002 GENERATE gamma(gender) and alpha(provres wbc gender)
preserve
keep if year==2002
merge 1:1 provres wbc using "$data\Fsingle_IRREG2002.dta"
tab _merge
recode Fsingle_tilde2002( .=0) if _merge==1
recode Fsingle_star ( .=1) 
gen legal2= wbc==0 | wbc==203 | wbc==206 | wbc==212 | wbc==214 | wbc==215 | wbc==216 | wbc==220 | wbc==221 | wbc==226 | wbc==232 | wbc==234 | wbc==219 | wbc==239 | wbc==240 
drop if legal2==1
drop _merge
egen Sum_Fsingle_tilde=total(Fsingle_tilde2002)
egen Sum_Fsingle_star = total(Fsingle_star)
gen gamma = (Sum_Fsingle_tilde / (Sum_Fsingle_tilde+Sum_Fsingle_star))/0.34
di gamma
bys provres wbc : gen alpha = Fsingle_tilde2002/ Fsingle_star
save "$data\Firregulars_2002.dta" , replace
restore

merge m:1 provres wbc using "$data\Firregulars_2002.dta", keepusing(alpha gamma Fsingle_tilde2002)
tab _merge
recode gamma (.=1.0042361) if _merge==1
recode alpha (.=0) 
drop _merge
recode Fsingle_star (.=0)
gen EU2004= wbc==257 | wbc==315 | wbc==247 | wbc==248 | wbc==249 | wbc==227 | wbc==233 | wbc==255 | wbc==251 | wbc==244 
gen EU2007= wbc==209 | wbc==235   
gen migrants=1
replace migrants=0  if EU2004==1&year>=2004
replace migrants=0  if EU2007==1&year>=2007
bys year: egen Sum_Fsingle_star = total(Fsingle_star) if migrants==1
gen Fsingle_star_alpha = Fsingle_star*alpha
bys year: egen Sum_Fsingle_star_alpha = total(Fsingle_star_alpha) if migrants==1

gen theta=.
replace theta=0.40 if year==1995
replace theta=0.25 if year==1996
replace theta=0.12 if year==1997
replace theta=0.19 if year==1998
replace theta=0.16 if year==1999
replace theta=0.11 if year==2000
replace theta=0.23 if year==2001
replace theta=0.23 if year==2003
replace theta=0.09 if year==2004
replace theta=0.15 if year==2005
replace theta=0.19 if year==2006
replace theta=0.10 if year==2007
replace theta=0.17 if year==2008
replace theta=0.10 if year==2009
replace theta=0.10 if year==2010
replace theta=0.09 if year==2011
replace theta=0.07 if year==2012

gen beta = ((gamma*theta)*Sum_Fsingle_star)/((1-gamma*theta)*(Sum_Fsingle_star_alpha)) if year!=2002
tab beta
replace alpha=0 if  EU2004==1&year>=2004
replace alpha=0 if  EU2007==1&year>=2007
replace beta=1 if  EU2004==1&year>=2004
replace beta=1 if  EU2007==1&year>=2007
save "$data\Fsingle_shock.dta" , replace

gen Fsingle = (1+alpha*beta)*Fsingle_star
replace Fsingle = Fsingle_star+Fsingle_tilde2002 if year==2002
codebook Fsingle
append using "$data\Fsingle_REGULARS.dta"
order Fsingle Fsingle_star Fsingle_tilde2002 alpha beta gamma theta
save "$data\Fsingle_FINAL.dta" , replace


* Robustness: Measurement error in Unofficial Singles 

forval s=10(10)300 {
	
use "$data\Fsingle_shock.dta" , clear
gen shock = `s'/100
gen Fsingle = (1+alpha*beta*shock)*Fsingle_star
replace Fsingle = Fsingle_star+Fsingle_tilde2002 if year==2002
codebook Fsingle
append using "$data\Fsingle_REGULARS.dta"

save "$data\Fsingle_FINAL_shock`s'.dta" , replace
}



* Robustness: Singles for cohabitations

use "$data\Msingle_FINAL.dta" , clear
rename hbc2 hbc
gen pre= year>=1997 & year<=2001
gen post= year>=2007 & year<=2011
collapse (sum) Msingle , by( hbc pre post provres)
gen year=.
replace year=2001 if pre==1
replace year=2011 if post==1
save "$data\Msingle_cohabitation_FINAL.dta" , replace

use "$data\Fsingle_FINAL.dta" , clear
rename wbc2 wbc
gen pre= year>=1997 & year<=2001
gen post= year>=2007 & year<=2011
collapse (sum) Fsingle , by( wbc pre post provres)
gen year=.
replace year=2001 if pre==1
replace year=2011 if post==1
save "$data\Fsingle_cohabitation_FINAL.dta" , replace

use "$data\Msingle_cohabitation_FINAL.dta" , clear
rename hbc eg
rename Msingle Msingle_for_wbc
lab var Msingle_for_wbc "Single males to merge with female nationality"
save "$data\Msingle_cohabitation_FINAL_merge2.dta" , replace

use "$data\Fsingle_cohabitation_FINAL.dta" , clear
rename wbc eg
rename Fsingle Fsingle_for_hbc
lab var Fsingle_for_hbc "Single females to merge with male nationality"
save "$data\Fsingle_cohabitation_FINAL_merge2.dta" , replace




***           TEST ASSUMPTIONS FOR PREDICT IRREGULARS         ***
***                           TEST 2                          ***

* REGULARS 

use "$data\singlemag_2001_country.dta" , clear
recode provincia ( 108 = 15) // change monza-brianza in MILANO
recode provincia ( 110 = 72) // change barletta-andria-trani e foggia in BARI 
recode provincia ( 71 = 72)  
recode provincia ( 91 = 92 ) // change oristano, nuoro, olbia-tempio, ogliastra, medio-campidano, carbonia-iglesias in CAGLIARI
recode provincia ( 95 = 92 )
recode provincia ( 104 = 92 )
recode provincia ( 105 = 92 )
recode provincia ( 106 = 92 )
recode provincia ( 107 = 92 )
gen y=2001
collapse (sum) single , by( y provincia bc agec sex) 
rename single single_reg 
rename provincia prov
drop if bc==0
save "$data\census2001_single_regular_test2.dta" , replace
 
 
* IRREGULARS

import excel "$data\irregulars_all_prov_nat_age_ms_gender_2002.xls", sheet("Sheet1") firstrow clear
* keep only singles
keep if ms==2 
drop ms
recode prov ( 108 = 15) // change monza-brianza in MILANO
recode prov ( 110 = 72) // change barletta-andria-trani e foggia in BARI 
recode prov  ( 71 = 72)  
recode prov ( 91 = 92 ) // change oristano, nuoro, olbia-tempio, ogliastra, medio-campidano, carbonia-iglesias in CAGLIARI
recode prov ( 95 = 92 )
recode prov ( 104 = 92 )
recode prov ( 105 = 92 )
recode prov ( 106 = 92 )
recode prov ( 107 = 92 )
rename males_all single1
rename females_all single2
rename nationality bc 
reshape long single , i(prov bc agec) j(sex)
rename single single_irr
drop if prov==.

merge 1:1 prov bc agec sex using "$data\census2001_single_regular_test2.dta" , keep(matched)
tab _merge
drop _merge

gen provres=prov
merge m:1 provres using "$data\population_byprovince.dta" , keep(match) 
tab _merge
drop _merge
collapse (sum) single_reg single_irr, by(prov bc sex population)
gen eu15 = 1 if bc==203 | bc==206 | bc==212 | bc==214 | bc==216 | bc==221 | bc==226 | bc==232 | bc==219  | bc==240 | bc==215 | bc==220 | bc==234 | bc==219 | bc==239 
drop if eu15==1
codebook single_reg
gen lreg = ln(1+single_reg)
codebook single_irr
gen lirr = ln(1+single_irr)


********************************************************************************
*  Figure D2 : Panel a. and b.
*  Test Second Assumption: Regular and Irregular Migrants

set more off
xtset prov 
eststo male: reg lirr lreg i.bc i.sex i.prov [aw=population] if sex==1 ,   vce(cluster prov) level(99)
test lreg=1
eststo female: reg lirr lreg i.bc i.sex i.prov [aw=population] if sex==2,   vce(cluster prov) level(99)
test lreg=1
est table *, keep(lreg) b(%6.3f) se(%6.3f) p(%6.4f)  stats(N r2 r2_a ) 
eststo clear

gen obs =1
collapse (mean) lirr lreg population (sum) obs , by(bc sex)

reg lirr lreg [aw=obs] if sex==1 
twoway (scatter lirr lreg if sex==1 , mcolor(black) ) (lfit lirr lreg [aw=obs] if sex==1, lcolor(cranberry)) , ///
graphregion(color(white)) xlab(0(1)5 , labs(small)) xtick(0(1)5) xsca(r(0(1)5)) ysca(r(0 5)) ylab(0(1)5) ylab( , labs(small) grid )  ///
ytitle("Log(Irregulars)" " " , size(*0.9)) xtitle("Log(Regulars)" " " , size(*0.9)) title("Migrant Males") legend(pos(4) ring(0) col(1) order(3 "Slope =1.01 (.068)"))
gr save  "$output\irregulars_test2_males" , replace
gr export "$output\irregulars_test2_males.pdf" , replace

reg lirr lreg [aw=obs] if sex==2
twoway (scatter lirr lreg if sex==2  , mcolor(black)) (lfit lirr lreg [aw=obs] if sex==2 , lcolor(cranberry)) , ///
graphregion(color(white)) xlab(0(1)5 , labs(small)) xtick(0(1)5) xsca(r(0(1)5)) ysca(r(0 5)) ylab(0(1)5) ylab( , labs(small) grid )  ///
ytitle("Log(Irregulars)" " " , size(*0.9)) xtitle("Log(Regulars)" " " , size(*0.9)) title("Migrant Females") legend(pos(4) ring(0) col(1) order(3 "Slope =0.95 (.086)"))
gr save  "$output\irregulars_test2_females" , replace
gr export "$output\irregulars_test2_females.pdf" , replace




***           TEST ASSUMPTIONS FOR PREDICT IRREGULARS         ***
***                           TEST 1                          ***

* SINGLE IRREGULARS

import excel "$data\irregulars_all_prov_nat_age_ms_gender_2002.xls", sheet("Sheet1") firstrow clear
* keep only singles
keep if ms==2 
drop ms
rename males_all single1
rename females_all single2
rename nationality bc 
reshape long single , i(prov bc agec) j(sex)
rename single single_irr
drop if prov==.
merge 1:1 prov bc agec sex using "$data\census2001_single_regular_test2.dta" , keep(matched)
tab _merge
drop _merge
collapse (sum) single_reg single_irr, by(prov bc sex)
gen eu15 = 1 if bc==203 | bc==206 | bc==212 | bc==214 | bc==216 | bc==221 | bc==226 | bc==232 | bc==219  | bc==240 | bc==215 | bc==220 | bc==234 | bc==219 | bc==239 
drop if eu15==1
gen lrsingle = ln(single_irr/single_reg)
save "$data\census2001_singleratio_test1.dta" , replace


* IMMIGRANT IRREGULARS, INDEPENDETLY FROM MARITAL STATUS 

import excel "$data\irregulars_all_prov_nat_age_ms_gender_2002.xls", sheet("Sheet1") firstrow clear
keep if ms!=4 // missing
rename nationality bc 
rename males_all irregular1
rename females_all irregular2
collapse (sum) irregular1 irregular2 , by(prov bc agec)
reshape long irregular , i(prov bc agec) j(sex)
drop if irregular==0
drop if prov==.

merge 1:1 prov bc agec sex using "$data\census2001_allregular_test1.dta" , keep(matched)
tab _merge
drop _merge
collapse (sum) irregular regular , by(prov bc sex)
gen eu15 = 1 if bc==203 | bc==206 | bc==212 | bc==214 | bc==216 | bc==221 | bc==226 | bc==232 | bc==219  | bc==240 | bc==215 | bc==220 | bc==234 | bc==219 | bc==239 
drop if eu15==1
gen lrtotal = ln(irregular/regular)

merge 1:1 prov bc sex using "$data\census2001_singleratio_test1.dta" , keep(matched)
tab _merge
drop _merge

gen provres=prov
merge m:1 provres using "$data\population_byprovince.dta" , keep(match) 
tab _merge
drop _merge
set matsize 10000


********************************************************************************
*  Figure D1 : panel a. and b.
*  Test First Assumption: Regular and Irregular Migrants

gen obs =1
collapse (mean) lrsingle lrtotal population (sum) obs , by(bc sex)

reg lrsingle lrtotal [aw=obs] if sex==1 
twoway (scatter lrsingle lrtotal if sex==1 , mcolor(black) ) (lfit lrsingle lrtotal [aw=obs] if sex==1, lcolor(cranberry)) , ///
graphregion(color(white)) xlab(-5(1)2 , labs(small)) xtick(-5(1)2) xsca(r(-5(1)2)) ysca(r(-5 2)) ylab(-5(1)2) ylab( , labs(small) grid )  ///
ytitle("Log(Irregular/Regular Total Migrants)" " " , size(*0.9)) xtitle("Log(Irregular/Regular Single Migrants)" " " , size(*0.9)) ///
title("Migrant Males") legend(pos(4) ring(0) col(1) order(3 "Slope =0.89 (.028)"))
gr save  "$output\irregulars_test1_males" , replace
gr export "$output\irregulars_test1_males.pdf" , replace

  
reg lrsingle lrtotal [aw=obs] if sex==2
twoway (scatter lrsingle lrtotal if sex==2  , mcolor(black)) (lfit lrsingle lrtotal [aw=obs] if sex==2 , lcolor(cranberry)) , ///
graphregion(color(white)) xlab(-5(1)2 , labs(small)) xtick(-5(1)2) xsca(r(-5(1)2)) ysca(r(-5 2)) ylab(-5(1)2) ylab( , labs(small) grid )  ///
ytitle("Log(Irregular/Regular Total Migrants)" " " , size(*0.9)) xtitle("Log(Irregular/Regular Single Migrants)" " " , size(*0.9)) ///
title("Migrant Females") legend(pos(4) ring(0) col(1) order(3 "Slope =0.93 (.031)"))
gr save  "$output\irregulars_test1_females" , replace
gr export "$output\irregulars_test1_females.pdf" , replace

  
